﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace CochinitaCorte//.CochinitaCorteData
{
    public class SimpleDatabaseValues
    {
        public string parameter { get; set; }
        public object value { get; set; }
    }

    public class MultipleDatabaseValues
    {
        public string SQL { get; set; }
        public List<SimpleDatabaseValues> rows { get; set; }
    }
       
    public class CustomSQL
    {   
        public List<SimpleDatabaseValues> Columns = new List<SimpleDatabaseValues>();
      
        public Clauses Clause { get; set; }
        public string Table { get; set; }
        public bool Where { get; set; }
        public string WhereFieldName { get; set; }
        public object WhereValue { get; set; }
        public bool OrderBy { get; set; }
        public string OrderByFieldName { get; set; }
        
        private string sqlString;

        public enum Clauses
        { 
           Select = 0,  
           Insert = 1,  
           Update = 2,  
           Delete = 3   
        }

        public string ProcessSQL()
        {
            if (Clause.Equals(Clauses.Update))
            {
                sqlString = Clauses.Update.ToString() + " " + Table + " Set ";
                
                foreach (var element in Columns)
                    sqlString += element.parameter + "=" + (typeof(string) == element.value.GetType() ? "'" + element.value + "'" : element.value) + ",";
                
                sqlString = sqlString.Remove(sqlString.Length - 1);
            }
            else if (Clause.Equals(Clauses.Delete))
            {
                sqlString = Clauses.Delete.ToString() + " FROM " + Table;
            }
            else if (Clause.Equals(Clauses.Insert))
            {
                string fields = "";
                string values = "";

                sqlString = Clauses.Insert.ToString() + " INTO " + Table + " (";

                foreach (var element in Columns)
                    fields += element.parameter + ",";

                fields = fields.Remove(fields.Length - 1);

                sqlString += fields + ") VALUES (";

                foreach (var element in Columns)
                    values += (typeof(string) == element.value.GetType() ? "'" + element.value + "'" : element.value) + ",";

                values = values.Remove(values.Length - 1);

                sqlString += values + ")";
                
                
            }

            //if (Clause.Equals(Clauses.Select))
            //{
            //    sqlString = Clauses.Select.ToString();

            //    //foreach (var element in Columns)
            //        sqlString += element.parameter + ",";

            //    sqlString = sqlString.Remove(sqlString.Length - 1);

            //    sqlString = sqlString + " FROM " + Table;
            //}

            if (Where)
                sqlString = sqlString + " Where " + WhereFieldName + " = " + (typeof(string) == WhereValue.GetType() ? "'" + WhereValue + "'" : WhereValue);

            return sqlString;
        }
  
    }
    
    class DatabaseManager
    {
        public OleDbDataAdapter Adapter;
        public const string Delimiter = "~";
        private DataTable dTable;
        private DataSet DS = new DataSet();

        OleDbCommandBuilder cmdBuilder;
        private OleDbConnection connection = new OleDbConnection();
        private const string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                @"Data source= DataBase\" +
                @"database.mdb";

        public DatabaseManager()
        {
            connection.ConnectionString = ConnectionString;
        }

        //public string[,] ReadFromDB(string SQL)//Devuelve Matriz Multidimansional
        //{
        //    OleDbCommand cmd = new OleDbCommand(SQL, connection);
        //    OleDbDataReader reader;
        //    reader = cmd.ExecuteReader();
        //    string[,] results;
        //    int row = 0;

        //    while (reader.Read())
        //    {
        //        for (int col = 0; col < reader.FieldCount; col++)
        //            temp += (x > 0 ? "," : "") + reader.GetValue(x).ToString();


        //        results[row, col] = new string[1, reader.FieldCount] { temp };

        //        row++;
        //    }
        //    reader.Close();

        //    return results;
        //}

        public string[] ReadFromDB(string SQL)
        {
            connection.Open();
            OleDbCommand cmd = new OleDbCommand(SQL, connection);
            OleDbDataReader reader;

            reader = cmd.ExecuteReader();
            List<string> results = new List<string>();


            while (reader.Read())
            {
                string temp = "";
                for (int x = 0; x < reader.FieldCount; x++)
                    temp += (x > 0 ? Delimiter : "") + reader.GetValue(x).ToString();

                results.Add(temp);
            }

            reader.Close();

            connection.Close();
            //return an array with CSV columns
            return results.ToArray();
        }

        public bool InsertToDB(string SQL, List<SimpleDatabaseValues> values)
        {
            //"Insert Into Contacts (FirstName, LastName) Values (?,?)"
            try
            {
                connection.Open();

                OleDbCommand cmd = new OleDbCommand(SQL, connection);

                cmd.CommandType = CommandType.Text;

                foreach (var data in values)
                    cmd.Parameters.AddWithValue(data.parameter, data.value);

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                connection.Close();
                return false;
            }
            connection.Close();
            return true;
        }

        public bool InsertToDBM(string SQL, List<List<SimpleDatabaseValues>> values)
        {
            //"INSERT INTO [Setting] ([Key], [Value]) VALUES (@key, @value);"

            using (OleDbTransaction Trans = connection.BeginTransaction())
            {
                using (OleDbCommand cmd = connection.CreateCommand())
                {
                    cmd.Transaction = Trans;
                    cmd.CommandText = SQL;
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        foreach (List<SimpleDatabaseValues> list in values)
                        {
                            foreach (SimpleDatabaseValues data in list)
                            {   //SqlDbType posible funcion
                                cmd.Parameters.AddWithValue(data.parameter, data.value);
                            }
                            cmd.ExecuteNonQuery();
                        }

                        Trans.Commit();
                        return true;
                    }
                    catch (Exception)
                    {
                        Trans.Rollback();
                        return false;
                        throw;
                    }

                }
            }
        }

        //public bool InsertToDBM(List<string> SQL, List<SimpleDatabaseValues> values)
        //{
        //    using (OleDbTransaction Trans = connection.BeginTransaction())
        //    {
        //        using (OleDbCommand cmd = connection.CreateCommand())
        //        {
        //            cmd.Transaction = Trans;
        //            cmd.CommandText = SQL;
        //            cmd.Connection = connection;
        //            cmd.CommandType = CommandType.Text;
        //            try
        //            {
        //                foreach (var data in values)
        //                  cmd.Parameters.AddWithValue(data.parameter, data.value);

        //                    cmd.ExecuteNonQuery();


        //                Trans.Commit();
        //                return true;
        //            }
        //            catch (Exception)
        //            {
        //                Trans.Rollback();
        //                return false;
        //                throw;
        //            }

        //        }
        //    }
        //}

        public bool ExecuteToDB(string SQL)
        {
            try
            {
                connection.Open();
                OleDbCommand cmd = new OleDbCommand(SQL, connection);
                cmd.CommandType = CommandType.Text;

                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                return false;
            }

            return true;
        }

        public bool UpdateToDB(List<string> SQLValues)
        {
            OleDbDataAdapter Adapter = new OleDbDataAdapter();

            try
            {
                connection.Open();
                Adapter.UpdateCommand = connection.CreateCommand();
                Adapter.UpdateCommand.Transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); 

                foreach (var sql in SQLValues)
                {
                    Adapter.UpdateCommand.CommandText = sql;
                    Adapter.UpdateCommand.ExecuteNonQuery();
                }

                Adapter.UpdateCommand.Transaction.Commit();
               
                connection.Close();
            }
            catch (Exception ex)
            {
                return false;
            }

            return true;
        }
     
        //ReadOnly
        public DataTable GridSourcer(string SQL)
        {
            //OleDbDataAdapter Adapter = new OleDbDataAdapter(SQL, ConnectionString);
            //OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder();
            OleDbCommand cmd = new OleDbCommand(SQL, connection);
            OleDbDataReader reader = cmd.ExecuteReader();
            DataTable dTable = new DataTable();

            dTable.Load(reader);

            return dTable;
        }

        public DataTable GridSourcer2(string SQL)
        {
            connection.Open();
            Adapter = new OleDbDataAdapter(SQL, ConnectionString);
            cmdBuilder = new OleDbCommandBuilder(Adapter);
            dTable = new DataTable();
            Adapter.Fill(dTable);
            //   connection.Close();
            return dTable;
        }

        public DataSet GridSourcer3(string SQL)
        {
            connection.Open();
            Adapter = new OleDbDataAdapter(SQL, connection);
            Adapter.Fill(DS);

            return DS;

        }
       
        public void AddGridChanges()
        {
            DataSet ds;
            cmdBuilder = new OleDbCommandBuilder(Adapter);
            ds = DS.GetChanges();
            if (ds != null)
            {
                Adapter.Update(DS.Tables[0]);
            }
            DS.AcceptChanges();

        }

        ~DatabaseManager()
        {
            try
            {
                connection.Close();
            }
            catch (Exception)
            { }
        }
    }
}